﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace Bainx.Data
{
    /// <summary>
    /// SQLite数据库操作类
    /// </summary>
    public class SQLite
    {
        private SQLiteConnection connection;
        private string connectionString;
        private string password;
        private string databaseFile;
        private Exception exception;

        /// <summary>
        /// 获取当某些方法返回false时填充这个异常信息
        /// </summary>
        public Exception Exception
        {
            get { return exception; }
        } 
        /// <summary>
        /// 获取或设置数据库密码
        /// </summary>
        public string Password
        {
            get { return password; }
            set
            {
                if (ChangePassword(value))
                    password = value;
            }
        }        
        /// <summary>
        /// 获取数据库文件路径与文件名
        /// </summary>
        public string DatabaseFile
        {
            get { return databaseFile; }
        }
        /// <summary>
        /// 获取数据库连接字符串
        /// </summary>
        public string ConnectionString
        {
            get { return connectionString; }
        }
        /// <summary>
        /// 实例化一个SQLite数据库
        /// </summary>
        /// <param name="DatabaseFile">数据库文件，如果文件不存在，将创建文件</param>
        /// <param name="Password">数据库密码</param>
        public SQLite(string DatabaseFile, string Password)
        {
            if (!System.IO.File.Exists(DatabaseFile))
            {
                CreateDatabase(DatabaseFile, Password);
            }
            else
            {
                Open(DatabaseFile, Password);
            }
        }

        /// <summary>
        /// 创建一个密码为Password的空的sqlite数据库
        /// </summary>
        /// <param name="DatabaseFile">文件路径与文件名</param>
        /// <param name="Password">密码</param>
        public void CreateDatabase(string DatabaseFile, string Password)
        {
            CreateDatabase(DatabaseFile);
            Open(DatabaseFile, null);
            connection.ChangePassword(Password);
        }
        /// <summary>
        /// 创建一个空sqlite数据库
        /// </summary>
        /// <param name="DatabaseFile">文件路径与文件名</param>
        public void CreateDatabase(string DatabaseFile)
        {
            //数据库名的后缀你可以直接指定，甚至没有后缀都可以
            //方法一：创建一个空sqlite数据库，用IO的方式
            //FileStream fs = File.Create(“c:\\test.db“);
            //方法二：用SQLiteConnection
            SQLiteConnection.CreateFile(DatabaseFile);
        }

        /// <summary>
        /// 改变数据库密码
        /// </summary>
        /// <param name="Password">密码</param>
        public bool ChangePassword(string Password)
        {
            bool ret = false;
            try
            {                
                connection.ChangePassword(Password);
                ret = true;              
            }
            catch (Exception ex)
            {
                exception = new Exception(String.Format("修改数据库{0}密码异常", databaseFile), ex);
                ret = false;
            }
            return ret;
        }

        /// <summary>
        /// 打开sqlite数据库
        /// </summary>
        /// <param name="DatabaseFile">文件路径与文件名</param>
        /// <param name="Password">密码</param>
        public bool Open(string DatabaseFile, string Password)
        {
            bool ret = false;
            try
            {
                //方法一
                databaseFile = DatabaseFile;
                password = Password;
                connectionString = String.Format("Data Source={0}", DatabaseFile);
                connection = new SQLiteConnection(connectionString);
                if (Password != "" && Password != null)
                    connection.SetPassword(Password);
                connection.Open();
                ret = true;
            }
            catch (Exception ex)
            {
                databaseFile = "";
                password = "";
                connectionString = "";
                connection = null;
                exception = new Exception(String.Format("打开数据库{0}异常", DatabaseFile), ex);
                ret = false;
            }
            return ret;
            ////方法二
            //SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
            //builder.DataSource = DatabaseFile;
            //builder.Password = Password;
            //SQLiteConnection cnn = new SQLiteConnection(builder.ConnectionString);
            //cnn .Open();
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool Open()
        {
            bool ret = false;
            try
            {
                connection.Open();
            }
            catch (Exception ex)
            {
                ret = false;
                exception = new Exception(String.Format("打开数据库{0}连接异常", databaseFile), ex);
            }
            return ret;
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        public bool Close()
        {
            bool ret = false;
            try
            {
                connection.Close();
                return ret;
            }
            catch (Exception ex)
            { 
                exception = new Exception(String.Format("关闭数据库{0}连接异常", databaseFile),ex);
                ret = false;
            }
            return ret;
        }
        /// <summary>
        /// 查询数据集
        /// </summary>
        /// <param name="SQLStr">查询语句</param>
        /// <param name="dataSet">返回的数据集</param>
        /// <returns></returns>
        public bool Select(string SQLStr, out DataSet dataSet)
        {
            bool ret = false;
            dataSet = new DataSet();
            try
            {
                SQLiteCommand a = new SQLiteCommand(SQLStr, connection);
                SQLiteDataAdapter adpa = new SQLiteDataAdapter(a);
                adpa.Fill(dataSet);
                ret = true;
            }
            catch (Exception ex)
            {
                ret = false;
                exception = new Exception(String.Format("查询数据集异常，SQL语句：{0}", SQLStr), ex);
            }
            return ret;
        }
        /// <summary>
        /// 查询数据集
        /// </summary>
        /// <param name="SQLStr">查询语句</param>
        /// <param name="dataTable">返回数据表</param>
        /// <returns></returns>
        public bool Select(string SQLStr, out DataTable dataTable)
        {
            bool ret = false;
            dataTable = new DataTable();
            try
            {
                SQLiteCommand a = new SQLiteCommand(SQLStr, connection);
                SQLiteDataAdapter adpa = new SQLiteDataAdapter(a);
                adpa.Fill(dataTable);
                ret = true;
            }
            catch (Exception ex)
            {
                ret = false;
                exception = new Exception(String.Format("查询数据集异常，SQL语句：{0}", SQLStr), ex);
            }
            return ret;
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="SQLStr">SQL语句</param>
        /// <returns>成功：插入的条目数，失败：-1</returns>
        public int Insert(string SQLStr)
        {
            int ret = -1;
            try
            {
                SQLiteCommand a = new SQLiteCommand(SQLStr, connection);
                ret = a.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ret = -1;
                exception = new Exception(String.Format("插入数据异常，SQL语句：{0}", SQLStr), ex);
            }
            return ret;
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="SQLStr">SQL语句</param>
        /// <returns>成功：更新的条目数，失败：-1</returns>
        public int Update(string SQLStr)
        {
            int ret = -1;
            try
            {
                SQLiteCommand a = new SQLiteCommand(SQLStr, connection);
                ret = a.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ret = -1;
                exception = new Exception(String.Format("更新数据异常，SQL语句：{0}", SQLStr), ex);
            }
            return ret;
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="SQLStr">SQL语句</param>
        /// <returns>成功：删除的条目数，失败：-1</returns>
        public int Detele(string SQLStr)
        {
            int ret = -1;
            try
            {
                SQLiteCommand a = new SQLiteCommand(SQLStr, connection);
                ret = a.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ret = -1;
                exception = new Exception(String.Format("删除数据异常，SQL语句：{0}", SQLStr), ex);
            }
            return ret;
        }



    }
}
